using System;
using System.Collections.Generic;
using System.Data;
using System.Data.SqlClient;

namespace DataAccess
{
	public class EmailSettingData
	{
		#region Fields
		protected static SqlConnection dbConn = new SqlConnection(Config.Global.Settings.ConnectionString);
		#endregion

		#region Get Object Method
		private static object getNullObject(object obj)
		{
			return obj == null ? DBNull.Value : obj;
		}
		private static object getNullValue(object obj)
		{
			return obj == DBNull.Value ? null : obj;
		}
		#endregion

		#region Constructors
		public EmailSettingData() {}
		#endregion

		#region Methods

		/// <summary>
		/// Saves a record to the EmailSetting table.
		/// </summary>
		/// <param name="emailSettingInfo"></param>
		/// <returns>ID of Saved record</returns>
		public static int? Insert(EmailSettingInfo emailSettingInfo)
		{
			SqlCommand dbCmd = new SqlCommand("[uspEmailSettingInsert]", dbConn);
			dbCmd.CommandType = CommandType.StoredProcedure;

			dbCmd.Parameters.AddWithValue("@SiteID", getNullObject(emailSettingInfo.SiteID));
			dbCmd.Parameters.AddWithValue("@EmailFrom", getNullObject(emailSettingInfo.EmailFrom));
			dbCmd.Parameters.AddWithValue("@SMTPUserName", getNullObject(emailSettingInfo.SMTPUserName));
			dbCmd.Parameters.AddWithValue("@SMTPPassword", getNullObject(emailSettingInfo.SMTPPassword));
			dbCmd.Parameters.AddWithValue("@SMTPServer", getNullObject(emailSettingInfo.SMTPServer));
			dbCmd.Parameters.AddWithValue("@SMTPPort", getNullObject(emailSettingInfo.SMTPPort));
			dbCmd.Parameters.AddWithValue("@SMTPSSL", getNullObject(emailSettingInfo.SMTPSSL));
			dbCmd.Parameters.AddWithValue("@EmailStatus", getNullObject(emailSettingInfo.EmailStatus));
			dbCmd.Parameters.AddWithValue("@RETURN_VALUE", SqlDbType.Int).Direction = ParameterDirection.ReturnValue;

			try
			{
				dbConn.Open();
				dbCmd.ExecuteNonQuery();
				return Convert.ToInt32(dbCmd.Parameters["@RETURN_VALUE"].Value);
			}
			catch (Exception)
			{
				return null;
			}
			finally
			{
				dbConn.Close();
			}
		}

		/// <summary>
		/// Updates a record in the EmailSetting table.
		/// </summary>
		/// <param name="emailSettingInfo"></param>
		/// <returns>true: successful; false: unsuccessful</returns>
		public static bool Update(EmailSettingInfo emailSettingInfo)
		{
			SqlCommand dbCmd = new SqlCommand("[uspEmailSettingUpdate]", dbConn);
			dbCmd.CommandType = CommandType.StoredProcedure;

			dbCmd.Parameters.AddWithValue("@EmailID", getNullObject(emailSettingInfo.EmailID));
			dbCmd.Parameters.AddWithValue("@SiteID", getNullObject(emailSettingInfo.SiteID));
			dbCmd.Parameters.AddWithValue("@EmailFrom", getNullObject(emailSettingInfo.EmailFrom));
			dbCmd.Parameters.AddWithValue("@SMTPUserName", getNullObject(emailSettingInfo.SMTPUserName));
			dbCmd.Parameters.AddWithValue("@SMTPPassword", getNullObject(emailSettingInfo.SMTPPassword));
			dbCmd.Parameters.AddWithValue("@SMTPServer", getNullObject(emailSettingInfo.SMTPServer));
			dbCmd.Parameters.AddWithValue("@SMTPPort", getNullObject(emailSettingInfo.SMTPPort));
			dbCmd.Parameters.AddWithValue("@SMTPSSL", getNullObject(emailSettingInfo.SMTPSSL));
			dbCmd.Parameters.AddWithValue("@EmailStatus", getNullObject(emailSettingInfo.EmailStatus));
			dbCmd.Parameters.AddWithValue("@RETURN_VALUE", SqlDbType.Int).Direction = ParameterDirection.ReturnValue;

			try
			{
				dbConn.Open();
				dbCmd.ExecuteNonQuery();
				return Convert.ToInt32(dbCmd.Parameters["@RETURN_VALUE"].Value)>0;
			}
			catch (Exception)
			{
				return false;
			}
			finally
			{
				dbConn.Close();
			}
		}

		/// <summary>
		/// Deletes a record from the EmailSetting table by its primary key.
		/// </summary>
		/// <param name="emailID"></param>
		/// <returns>true: successful; false: unsuccessful</returns>
		public static bool DeleteByEmailID(int emailID)
		{
			SqlCommand dbCmd = new SqlCommand("[uspEmailSettingDeleteByEmailID]", dbConn);
			dbCmd.CommandType = CommandType.StoredProcedure;

			dbCmd.Parameters.AddWithValue("@EmailID", emailID);
			dbCmd.Parameters.AddWithValue("@RETURN_VALUE", SqlDbType.Int).Direction = ParameterDirection.ReturnValue;

			try
			{
				dbConn.Open();
				dbCmd.ExecuteNonQuery();
				return Convert.ToInt32(dbCmd.Parameters["@RETURN_VALUE"].Value)>0;
			}
			catch (Exception)
			{
				return false;
			}
			finally
			{
				dbConn.Close();
			}
		}

		/// <summary>
		/// Deletes a record from the EmailSetting table by a foreign key.
		/// </summary>
		/// <param name="siteID"></param>
		/// <returns>true: successful; false: unsuccessful</returns>
		public static bool DeleteAllBySiteID(int siteID)
		{
			SqlCommand dbCmd = new SqlCommand("[uspEmailSettingDeleteAllBySiteID]", dbConn);
			dbCmd.CommandType = CommandType.StoredProcedure;

			dbCmd.Parameters.AddWithValue("@SiteID", siteID);
			dbCmd.Parameters.AddWithValue("@RETURN_VALUE", SqlDbType.Int).Direction = ParameterDirection.ReturnValue;

			try
			{
				dbConn.Open();
				dbCmd.ExecuteNonQuery();
				return Convert.ToInt32(dbCmd.Parameters["@RETURN_VALUE"].Value)>0;
			}
			catch (Exception)
			{
				return false;
			}
			finally
			{
				dbConn.Close();
			}
		}

		/// <summary>
		/// Selects a single record from the EmailSetting table.
		/// </summary>
		/// <param name="emailID"></param>
		/// <returns>EmailSettingInfo: A record from the EmailSetting table by its primary key.</returns>
		public static EmailSettingInfo SelectByEmailID(int emailID)
		{
			SqlCommand dbCmd = new SqlCommand("[uspEmailSettingSelectByEmailID]", dbConn);
			dbCmd.CommandType = CommandType.StoredProcedure;
			EmailSettingInfo emailSettingInfo =  new EmailSettingInfo();

			dbCmd.Parameters.AddWithValue("@EmailID", emailID);

			try
			{
				dbConn.Open();
				SqlDataReader dataReader = dbCmd.ExecuteReader();

				if (dataReader.Read())
				{
					emailSettingInfo.EmailID = System.Convert.ToInt32(getNullValue(dataReader["EmailID"]));
					emailSettingInfo.SiteID = System.Convert.ToInt32(getNullValue(dataReader["SiteID"]));
					emailSettingInfo.EmailFrom = System.Convert.ToString(getNullValue(dataReader["EmailFrom"]));
					emailSettingInfo.SMTPUserName = System.Convert.ToString(getNullValue(dataReader["SMTPUserName"]));
					emailSettingInfo.SMTPPassword = System.Convert.ToString(getNullValue(dataReader["SMTPPassword"]));
					emailSettingInfo.SMTPServer = System.Convert.ToString(getNullValue(dataReader["SMTPServer"]));
					emailSettingInfo.SMTPPort = System.Convert.ToInt32(getNullValue(dataReader["SMTPPort"]));
					emailSettingInfo.SMTPSSL = System.Convert.ToBoolean(getNullValue(dataReader["SMTPSSL"]));
					emailSettingInfo.EmailStatus = System.Convert.ToBoolean(getNullValue(dataReader["EmailStatus"]));
				}

				dataReader.Close();
				return emailSettingInfo;
			}
			catch (Exception)
			{
				return new EmailSettingInfo();
			}
			finally
			{
				dbConn.Close();
			}
		}

		/// <summary>
		/// Selects all records from the EmailSetting table.
		/// </summary>
		/// <return>DataTable</return>
		public static DataTable SelectAll()
		{
			SqlCommand dbCmd = new SqlCommand("[uspEmailSettingSelectAll]",dbConn);
			dbCmd.CommandType = CommandType.StoredProcedure;
			DataTable dtbEmailSetting =  new DataTable();

			try
			{
				dbConn.Open();
				SqlDataReader dataReader = dbCmd.ExecuteReader();

				if (dataReader.HasRows)
				{
					dtbEmailSetting.Load(dataReader);
				}

				dataReader.Close();
				return dtbEmailSetting;
			}
			catch (Exception)
			{
				return null;
			}
			finally
			{
				dbConn.Close();
			}
		}

		/// <summary>
		/// Selects all records from the EmailSetting table by a foreign key.
		/// </summary>
		/// <param name="siteID"></param>
		/// <return>All records from the EmailSetting table by a foreign key.</return>
		public static DataTable SelectAllBySiteID(int siteID)
		{
			SqlCommand dbCmd = new SqlCommand("[uspEmailSettingSelectAllBySiteID]", dbConn);
			dbCmd.CommandType = CommandType.StoredProcedure;
			DataTable dtbEmailSetting =  new DataTable();

			dbCmd.Parameters.AddWithValue("@SiteID", siteID);

			try
			{
				dbConn.Open();
				SqlDataReader dataReader = dbCmd.ExecuteReader();

				if (dataReader.HasRows)
				{
					dtbEmailSetting.Load(dataReader);
				}

				dataReader.Close();
				return dtbEmailSetting;
			}
			catch (Exception)
			{
				return null;
			}
			finally
			{
				dbConn.Close();
			}
		}

		#endregion
	}
}
